package com.pcjavanet.collect.mes.dao.impl.jdbc.postgresql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.pcjavanet.collect.mes.dao.AbstractDao;
import com.pcjavanet.collect.mes.dao.OriginalDataDao;
import com.pcjavanet.collect.mes.to.ContinuousOriginalData;
import com.pcjavanet.collect.mes.to.DiscreteOriginalData;
import com.pcjavanet.collect.mes.to.ParetoData;
import com.pcjavanet.collect.mes.util.CommonServerUtil;
import com.pcjavanet.collect.mes.util.ConvertFormula;
import com.pcjavanet.collect.mes.util.SystemConstant;
import com.pcjavanet.domain.Attribute;
import com.pcjavanet.domain.SPCObject;


/**
 * class OriginalDataDaoImpl.
 * @version 3.0
 * @author kenny_li
 */
public class OriginalDataDaoImpl  extends AbstractDao implements OriginalDataDao{
	
	protected static Logger logger = Logger.getLogger(OriginalDataDaoImpl.class);
 
	/**
	 * get discrete original data database,include C,U,P and NP chart.
	 * @param monitor -Monitor
	 * @param lastGroupTime -int
	 * @return List<DiscreteOriginalData> -discreteOrignDataList
	 * @throws DataAccessException -exception
	 */
	public List<DiscreteOriginalData> getDiscreteOriginalData(SPCObject monitor, int lastGroupTime) throws DataAccessException {
		final List<DiscreteOriginalData> discreteOriginalDataList = new ArrayList<DiscreteOriginalData>();
//		int lastGroupTime = monitor.getLastGroupTime();
//		if(isFPYChart){
//			lastGroupTime = monitor.getLastGroupTime2();
//		}
		if ( SystemConstant.MONITOR_GROUP_TYPE_BY_BATCH.equals( monitor.getGroupingType())) {
//	        String partNumber=object.getPartNumber();
	      //  String familyName=object.getFamilyName();
	        String workstation = "";
	        String partCondition = "";
	        //sql fetch fields [ serial-key ,serial_number , numcomps , action , to_timestamp  ]
	        if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS)){
				workstation="  AND l.process_name ='"+monitor.getShortName()+"' ";
			}else{
				if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_WORKSTATION)){
					workstation = " AND l.workstation ='"+monitor.getShortName()+"' ";
				}else{
//					String locationFamily = monitor.getExtf1();
//					locationFamily=locationFamily.replaceAll(",", "','");
//					locationFamily="'"+locationFamily+"'";
//					workstation="  AND l.workstation in ("+locationFamily+") ";
				}
			}
//			
//			if ( familyName != null && !familyName.equals(""))
//			if (CommonServerUtil.judgeFamily(monitor.getPartNumber(), monitor.getPartFamilyName()) ){
//				partCondition = " AND p.family_id='"+monitor.getPartFamilyName()+"' ";
//			}else{
//				partCondition ="  AND p.part_number='"+monitor.getPartNumber()+"' ";
//			}
//			
			String lastGroupTimeCondition = " AND a.\"a_DateTime\">" + lastGroupTime;
	        String sql = BatchSupport.getBatchSerialSQL(workstation, partCondition, lastGroupTimeCondition, monitor.getNetUserId());
//	        if ( logger.isInfoEnabled()  &&  ViewSpcMemoryAtion.isInInfoMonitor( monitor.getMonitorId() ) ){
//	        	logger.info(" static data getDiscreteOriginalData:"+sql );
//	        }
			long startTime = System.currentTimeMillis();
			try {
				jdbcTemplate.query(
						sql.toString(),
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							DiscreteOriginalData discreteOriginalData = new DiscreteOriginalData();
							discreteOriginalData.setAction( rs.getInt("action") );
							int moduleSelect = rs.getInt("module_SELECT");
							discreteOriginalData.setModuleSelect( moduleSelect );
//							discreteOriginalData.setSerialKey(rs.getInt("serial-key"));				
//							String lastDate=getLastDate(rs.getString(2),rs.getInt(3));
//							spcChartData.setYieldTime(lastDate);
							//2010-12-03 19:00:08+08 -->2010-12-03 19:00:08 
							discreteOriginalData.setYieldTime(rs.getString("to_timestamp").substring(0, 19));
//							spcChartData.setADateTime(dateTime)
							if(rs.getInt("action")==11){	//action=11 represents S/N passed
								discreteOriginalData.setPass(1);
								discreteOriginalData.setFail(0);
								
								if ( moduleSelect == 8 )
									discreteOriginalData.setTotal( rs.getInt("quantity") );
								else 
									discreteOriginalData.setTotal( rs.getInt("numcomps") );
								
								discreteOriginalData.setDefects(0);
							}else{
								discreteOriginalData.setPass(0);
								discreteOriginalData.setFail(0);
								if ( moduleSelect == 8 )
									discreteOriginalData.setTotal( rs.getInt("quantity") );
								else 
									discreteOriginalData.setTotal( rs.getInt("numcomps") );
//								spcChartData.setTotal(0); //0 for other mother will do it				
							}		
							discreteOriginalData.setADateTime(rs.getInt("a_DateTime"));
							discreteOriginalData.setPartNumber(rs.getString("serial_number"));// batch serial number
							discreteOriginalDataList.add(discreteOriginalData);
						}
					}
				);
			}catch (Exception e) {
				logger.error( e );
				
			}
		} else {
//			String strDateBegin = monitor.getLastGroupDate().toString();
			StringBuffer buf = new StringBuffer();
			buf.append(" select yield_date,yield_time,action AS passed,\"a_DateTime\",\"location-key\",part_number,serial_number ");
			buf.append(" from  v_serial_activity_defect ");
			buf.append(" WHERE (action=11 or action=12) " );
			if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS)){
				buf.append(" AND process_name ='"+monitor.getShortName()+"' ");
			}else{
				if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_WORKSTATION)){
					buf.append(" AND workstation ='"+monitor.getShortName()+"' ");
				}else{
//					String locationFamily = monitor.getExtf1();
//					locationFamily=locationFamily.replaceAll(",", "','");
//					locationFamily="'"+locationFamily+"'";
//					buf.append(" AND workstation in ("+locationFamily+") ");
				}
			}
//			if (CommonServerUtil.judgeFamily(monitor.getPartNumber(), monitor.getPartFamilyName()) ){
//				buf.append(" AND family_id='"+monitor.getPartFamilyName()+"' ");
//			}else{
//				buf.append(" AND part_number='"+monitor.getPartNumber()+"' ");
//			}
			buf.append(" AND anet_userid='"+monitor.getNetUserId()+"' ");
			buf.append(" AND \"a_DateTime\">"+lastGroupTime);
			buf.append(" ORDER BY \"a_DateTime\" limit 15000 ");
//			if ( logger.isInfoEnabled()  &&  ViewSpcMemoryAtion.isInInfoMonitor( monitor.getMonitorId() ) ){
//				logger.info(" static data getDiscreteOriginalData:"+buf.toString());
//			}
			long startTime = System.currentTimeMillis();
			try {
				jdbcTemplate.query(
					buf.toString(),
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							DiscreteOriginalData discreteOriginalData = new DiscreteOriginalData();
//							discreteOriginalData.setSerialKey(rs.getInt(1));				
							String lastDate=getLastDate(rs.getString(1),rs.getInt(2));
							discreteOriginalData.setYieldTime(lastDate);
							if(rs.getInt(3)==11){
								discreteOriginalData.setPass(1);
								discreteOriginalData.setFail(0);
							}else{
								discreteOriginalData.setPass(0);
								discreteOriginalData.setFail(1);
							}		
							discreteOriginalData.setAction(rs.getInt(3));
							discreteOriginalData.setADateTime(rs.getLong(4));
							discreteOriginalData.setLocationKey(rs.getInt(5));
							discreteOriginalData.setPartNumber(rs.getString(6));
							discreteOriginalData.setSerialNumber(rs.getString(7));
							discreteOriginalData.setTotal(1);				
							discreteOriginalDataList.add(discreteOriginalData);
						}
					}
				);
 
		} catch ( Exception e ) {
				logger.error( e );
		}
		}
		return discreteOriginalDataList;
	}
	
	/**
	 * get continuous original data database,include X-Bar,R,I,MR and Cpk/ppk chart.
	 * @param monitor -Monitor
	 * @param strDateEnd -String
	 * @return List<ContinuousOriginalData> -continuousOriginalDataList
	 * @throws DataAccessException -exception
	 */
	public List<ContinuousOriginalData> getContinuousOriginalData(SPCObject monitor) throws DataAccessException {
		final List<ContinuousOriginalData> continuousOriginalDataList = new ArrayList<ContinuousOriginalData>();
//		String attribute=monitor.getAttributeName();
//		attribute=attribute.replaceAll(",", "','");
//		attribute="'"+attribute+"'";
		List<Attribute> attributes=monitor.getAttributes();
		StringBuffer bufAtt = new StringBuffer();
		for( int i=0; i<attributes.size();i++) {
			bufAtt.append("'").append(attributes.get(i).getAttributeName()).append("'");
			if ( i !=( attributes.size() -1 ) ) {
				bufAtt.append(",");
			}  
		}
		String  attribute = bufAtt.toString() ;
		if(!attribute.equals("''")){
//			String strDateBegin = monitor.getLastGroupDate().toString().substring(0, 19);
			long lastGroupTime = monitor.getLastAccessTime();
			StringBuffer buf = new StringBuffer();
			buf.append(" select serial_number, cparam1,  cparam2, yield_date,yield_time,\"a_DateTime\",part_number ");
			buf.append(" FROM v_serial_activity_defect  where ");
			if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS)){
				buf.append(" process_name ='"+monitor.getShortName()+"' ");
			}else{
				if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_WORKSTATION)){
					buf.append(" workstation ='"+monitor.getShortName()+"' ");
				}else{
//					String locationFamily = monitor.getExtf1();
//					locationFamily=locationFamily.replaceAll(",", "','");
//					locationFamily="'"+locationFamily+"'";
//					buf.append(" workstation in ("+locationFamily+") ");
				}
			}
//			if (CommonServerUtil.judgeFamily(monitor.getPartNumber(), monitor.getPartFamilyName()) ){
//				buf.append(" AND family_id='"+monitor.getPartFamilyName()+"' ");
//			}else{
//				buf.append(" AND part_number='"+monitor.getPartNumber()+"' ");
//			}
			buf.append(" AND cparam1 in ( "+attribute+" )" );
			buf.append(" AND anet_userid='"+monitor.getNetUserId()+"' ");
			buf.append(" AND \"a_DateTime\">"+lastGroupTime);
			buf.append(" ORDER BY \"a_DateTime\" limit 15000 ");
 
			long startTime = System.currentTimeMillis();
			try {	
				jdbcTemplate.query(
					buf.toString(),	
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							ContinuousOriginalData continuousOriginalData = new ContinuousOriginalData();
//							continuousOriginalData.setSerialKey(rs.getInt(1));	
							continuousOriginalData.setSerialNumber(rs.getString(1));
							continuousOriginalData.setParam1(rs.getString(2));
							continuousOriginalData.setParam2(rs.getString(3));
							continuousOriginalData.setYieldDate(rs.getString(4));
							continuousOriginalData.setYieldTime(rs.getInt(5));
							continuousOriginalData.setADateTime(rs.getLong(6));
							continuousOriginalData.setPartNumber(rs.getString(7));
							continuousOriginalDataList.add(continuousOriginalData);
						}
					}
				);
 
			}catch (Exception e) {
				logger.error(  e );
			}
		}
		return continuousOriginalDataList;
	}
	
	public List<DiscreteOriginalData> getDefectDiscreteOriginalData(SPCObject monitor, int lastGroupTime, long lastDataTime){
		
		final List<DiscreteOriginalData> discreteOriginalDataList = new ArrayList<DiscreteOriginalData>();
		StringBuffer buf = new StringBuffer();
		buf.append(" select serial_number, \"a_DateTime\",defectseq,defectcode,defectqty ");
		buf.append(" FROM v_serial_activity_defect  ");
		buf.append(" WHERE defectcode NOT LIKE 'B%'   and action=41 ");//action=41 represents a original defect data
		if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS)){
			buf.append(" AND process_name ='"+monitor.getShortName()+"' ");
		}else{
			if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_WORKSTATION)){
				buf.append(" AND workstation ='"+monitor.getShortName()+"' ");
			}else{
//				String locationFamily = monitor.getExtf1();
//				locationFamily=locationFamily.replaceAll(",", "','");
//				locationFamily="'"+locationFamily+"'";
//				buf.append(" AND workstation in ("+locationFamily+") ");
			}
		}
		
//		if (CommonServerUtil.judgeFamily(monitor.getPartNumber(), monitor.getPartFamilyName()) ){
//			buf.append(" AND family_id='"+monitor.getPartFamilyName()+"' ");
//		}else{
//			buf.append(" AND part_number='"+monitor.getPartNumber()+"' ");
//		}
		buf.append(" AND anet_userid='"+monitor.getNetUserId()+"' ");
		buf.append(" AND \"a_DateTime\">"+lastGroupTime);
		buf.append(" AND \"a_DateTime\"<="+lastDataTime);//将这个时间段里所有的 original defect data 得到
		buf.append(" ORDER BY \"a_DateTime\" ");
 
		long startTime = System.currentTimeMillis();
		try {
			jdbcTemplate.query(
				buf.toString(),				
				new RowCallbackHandler() {	
					public void processRow(ResultSet rs) throws SQLException {
						DiscreteOriginalData discreteOriginalData = new DiscreteOriginalData();
						discreteOriginalData.setSerialNumber(rs.getString(1));
//						discreteOriginalData.setSerialKey(rs.getInt(1));	
						discreteOriginalData.setADateTime(rs.getLong(2));
						discreteOriginalData.setDefects(rs.getInt(5));	
						discreteOriginalDataList.add(discreteOriginalData);
					}	
				}
			);
 
		} catch (Exception e) {
			logger.error ( e );
		}
		return discreteOriginalDataList;
	}
	
	
	/**
	 * 
	 *	serial-key	serial_number	action 	defectcode 	defectqty 	to_timestamp 
	 *  1100678		"6A2011RP-01"	41		"K047"		1			"2010-12-03 18:59:43+08"
	 *	1100678		"6A2011RP-01"	41		"K058"		1			"2010-12-03 18:59:55+08"
	 *
	 * fetch one batch's in Range time done qty. need to get one record timestamp and qty. 
	 * in other method will use timestamp to get unit qty and then  unit qty * batch qty 
	 * @param id
	 * @param batchSerialNumber
	 * @param strDateEnd
	 */
	public  List<DiscreteOriginalData> getOneBatchFailProcessNumbers(final SPCObject monitor,final String  batchSerialNumber,final String strDateEnd)  throws DataAccessException {
		final List<DiscreteOriginalData> discreteOriginalDataList =   new LinkedList<DiscreteOriginalData>();
		String workstation = "";
//		if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_WORKSTATION)){
//			workstation = " AND l.workstation ='"+monitor.getMonitorShortName()+"' ";
//		}else{
//			workstation="  AND l.process_name ='"+monitor.getMonitorShortName()+"' ";
//		}
		if( monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS) ){
			workstation="  AND l.process_name ='"+monitor.getShortName()+"' ";
		}else{
			if( monitor.getMonitorType().equals( SystemConstant.MONITOR_TYPE_WORKSTATION )){
				workstation = " AND l.workstation ='"+monitor.getShortName()+"' ";
			}else{
//				String locationFamily = monitor.getExtf1();
//				locationFamily=locationFamily.replaceAll(",", "','");
//				locationFamily="'"+locationFamily+"'";
//				workstation="  AND l.workstation in ("+locationFamily+") ";
			}
		}
		//sql fetch fields [ serial-key , serial_number , action , defectcode , defectqty , to_timestamp ]
		 String sql = BatchSupport.getOneBatchFailProcessNumbers(workstation, batchSerialNumber, strDateEnd,monitor.getNetUserId());
//		 if ( logger.isInfoEnabled()  &&  ViewSpcMemoryAtion.isInInfoMonitor( monitor.getMonitorId() ) ){
//			 logger.info(" static data getOneBatchSerialFailQty(BB):"+sql );
//		 }
			
		try {
			jdbcTemplate.query(
					sql.toString(),
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							DiscreteOriginalData discreteOriginalData = new DiscreteOriginalData();
//							discreteOriginalData.setSerialKey(rs.getInt("serial-key"));				
							discreteOriginalData.setYieldTime(rs.getString("to_timestamp").substring(0, 19));
							discreteOriginalData.setPass(0);
							discreteOriginalData.setFail( rs.getInt("defectqty") );
							discreteOriginalData.setTotal(0);  			
							discreteOriginalData.setADateTime(rs.getTimestamp("to_timestamp").getTime());
							discreteOriginalData.setPartNumber(rs.getString("serial_number"));// batch serial number
							discreteOriginalDataList.add(discreteOriginalData);
						}
					}
				);
			}catch (Exception e) {
				logger.error( e );
			}
			return discreteOriginalDataList;
	}
	 
 
	
	/**
	 * get LastDate.
	 * @param strYieldDate -String
	 * @param yieldTime -int
	 * @return lastDate -String
	 */
	private String getLastDate(String strYieldDate,int yieldTime){
		
		Date yieldDate=  ConvertFormula.parseStrDateUseLenFormate( strYieldDate );
    	Date yTime = CommonServerUtil.joinDate(yieldDate,yieldTime);
       String lastDate=  ConvertFormula.formateDateToLenStr( yTime );
       return lastDate;
       
	}

	public List<ParetoData> getParetoOriginalData(final SPCObject monitor,	int lastGroupTime) {	
		String workstation = ""; 
		if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_PROCESS)){
			workstation="  process_name ='"+monitor.getShortName()+"' ";
		}else{
			if(monitor.getMonitorType().equals(SystemConstant.MONITOR_TYPE_WORKSTATION)){
				workstation = " workstation = '"+monitor.getShortName()+"' ";
			}else{
//				String locationFamily = monitor.getExtf1();
//				locationFamily=locationFamily.replaceAll(",", "','");
//				locationFamily="'"+locationFamily+"'";
//				workstation=" workstation in ("+locationFamily+") ";
			}
		}
		workstation = workstation + " AND anet_userid = '"+ monitor.getNetUserId()+"'  ";
//		workstation = workstation + " AND anet_userid = '"+ monitor.getNetUserId()+"' AND l.aactive=0 ";
		String partNumberType = "";
//		if (CommonServerUtil.judgeFamily( monitor.getPartNumber(), monitor.getPartFamilyName()) ){
//			partNumberType = "  family_id = '"+monitor.getPartFamilyName()+"' ";
//		}else{
//			partNumberType = "  part_number='"+monitor.getPartNumber()+"' " ;
//		}
		partNumberType = partNumberType+ " AND anet_userid ='"+ monitor.getNetUserId()+"' ";
//		partNumberType = partNumberType+ " AND anet_userid ='"+ monitor.getNetUserId()+"' AND aactive=0 ";
		
		final List<ParetoData> datas =   new LinkedList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer ();        
//		sqlBuf.append(" SELECT a.\"serial-key\" as a_serial_key ,a.\"part-key\" as a_part_key , s.serial_number as s_serial_number ");
//		sqlBuf.append("        , sf.defectcode as sf_defectcode, a.\"location-key\" as a_location_key , a.\"a_DateTime\" as a_a_datetime");
//		sqlBuf.append(" FROM activity  a ,serdef sf ,serial s "); 
//		sqlBuf.append("  WHERE   a.action=41       ");  
//		sqlBuf.append("  AND a.\"part-key\" IN ( ");
//		sqlBuf.append("		SELECT DISTINCT \"part-key\"  FROM part  WHERE ").append( partNumberType );
//		sqlBuf.append("	 ) "); 
//		sqlBuf.append("  AND a.\"part-key\"=sf.\"part-key\" AND sf.\"part-key\"=s.\"part-key\" ");  
//		sqlBuf.append("  AND a.\"a_DateTime\" >  ").append( lastGroupTime );       
//		sqlBuf.append("  AND a.\"a_DateTime\"<=  ( SELECT INT4( EXTRACT(EPOCH FROM ( SELECT date_trunc('second',now() ) ) ) ) )   ");  
//		sqlBuf.append("  AND a.\"serial-key\"=sf.\"serial-key\" AND a.iparam1=sf.defectseq AND defectcode NOT LIKE 'B%'");       
//		sqlBuf.append("  AND a.\"serial-key\"=s.\"serial-key\"  AND    a.\"part-key\"=s.\"part-key\"   ");     
//		sqlBuf.append("  AND a.\"location-key\" IN ( ");       
//		sqlBuf.append("  	SELECT DISTINCT  \"location-key\"  FROM location l WHERE      ").append(workstation);  
//		sqlBuf.append("	 )");   
//		sqlBuf.append("  AND a.aactive = sf.aactive  AND a.aactive = s.aactive AND sf.aactive = s.aactive ");  
//		sqlBuf.append("  AND sf.aactive=0 and   a.aactive=0  AND s.aactive=0       ");             
//		sqlBuf.append("  AND sf.anet_userid='").append(monitor.getNetUserId()).append("' AND   a.anet_userid='").append(monitor.getNetUserId()).append("'  AND s.anet_userid='").append(monitor.getNetUserId()).append("'   "); 
//		sqlBuf.append("  AND  a.anet_userid=sf.anet_userid  AND a.anet_userid=s.anet_userid AND sf.anet_userid=s.anet_userid  AND a.alot=sf.alot AND a.alot=s.alot AND sf.alot=s.alot ");     
//		sqlBuf.append(" ORDER BY a.\"a_DateTime\" ASC");  
		sqlBuf.append("  SELECT  defectqty,serial_number as s_serial_number, defectcode as sf_defectcode ,\"location-key\" as a_location_key ,\"a_DateTime\" as a_a_datetime	" );
		sqlBuf.append("  FROM v_serial_activity_defect " ); 
		sqlBuf.append("  WHERE   defectcode NOT LIKE 'B%' AND action=41  AND  \"a_DateTime\" >  ").append( lastGroupTime );       
//		sqlBuf.append("   AND \"a_DateTime\"<=  ( SELECT INT4( EXTRACT(EPOCH FROM ( SELECT date_trunc('second',now() ) ) ) ) )   ");  
		sqlBuf.append( "  AND ").append( workstation );
		sqlBuf.append("   AND" ).append( partNumberType );
		sqlBuf.append("  ORDER BY \"a_DateTime\" ASC");  
//		if ( logger.isInfoEnabled()  &&  ViewSpcMemoryAtion.isInInfoMonitor( monitor.getMonitorId() ) ){
//			logger.info( sqlBuf.toString());
//		}
		long startTime = System.currentTimeMillis();
		try {
			jdbcTemplate.query(
				sqlBuf.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
							ParetoData bean = new ParetoData();
							bean.setDefectqty(  rs.getInt("defectqty") ) ;
							bean.setaDateTime(  rs.getInt("a_a_datetime") );
							bean.setDefectcode( rs.getString("sf_defectcode") );
	//						bean.setAactive(rs.getInt("a_aactive"));
	//						bean.setAlot(rs.getInt("a_alot"));
	//						bean.setSerialKey(rs.getString("a_serial_key"));
	//						bean.setPartKey( rs.getString("a_part_key"));
							bean.setLocationKey( rs.getString("a_location_key"));
							bean.setSerialNumber( rs.getString( "s_serial_number") );
							bean.setMonitorkey( monitor.getObjectKey() );
							bean.setDescription("");
							datas.add(bean);
					}
				}
			);
 
		}catch (Exception e) {
			logger.error( e );
		}
		
		if ( datas.size() > 0 ) {
			StringBuffer defcodes = new StringBuffer();
			defcodes.append(" 1=1 ");
			
			//for filter defectode first put map,use map key is unique
			Map<String,String> defectcodesMap =new  HashMap<String, String>();
			for( int i = 0 ; i < datas.size() ; i++ ){
				defectcodesMap.put( datas.get(i).getDefectcode() , datas.get(i).getDefectcode() );
			}	
			List<String> defectcodeLs = new ArrayList<String>();
			if ( defectcodesMap.size() > 0 ){
				java.util.Iterator<String> keys = defectcodesMap.keySet().iterator();
				while (keys.hasNext()){
					defectcodeLs.add( keys.next() );
				}
			}
	
			for( int i = 0 ; i < defectcodeLs.size() ; i++ ){
					if ( i== 0 )
						defcodes.append( "AND  defectcode IN (");
					if ( i == (defectcodeLs.size() -1 ) ){
						defcodes.append("'").append( defectcodeLs.get(i) ).append("'");
						defcodes.append( " )");
					} else {
						defcodes.append("'").append( defectcodeLs.get(i) ).append("' , ");
					}
			}
		 
			StringBuffer defcodesSql = new StringBuffer();
			defcodesSql.append("SELECT DISTINCT defectcode,description FROM defcodes   WHERE  ").append(defcodes.toString()).append("  ");
			defcodesSql.append("  AND anet_userid = '").append( monitor.getNetUserId() ).append("' AND  aactive=0 ");
			final List<ParetoData> defectDatas =   new LinkedList<ParetoData>();
			startTime = System.currentTimeMillis();
			try {
				jdbcTemplate.query(
						defcodesSql.toString(),
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							ParetoData bean = new ParetoData();
							bean.setDefectcode( rs.getString("defectcode") );
	//						bean.setAactive(rs.getInt("a_aactive"));
	//						bean.setAlot(rs.getInt("a_alot"));
							bean.setDescription( rs.getString("description") );
							defectDatas.add(bean);
						}
					}
				);
 
			}catch (Exception e) {
				logger.error( e );
			}
			
			for( int i = 0 ; i < datas.size() ; i++ ){
				for( int j = 0 ; j < defectDatas.size() ; j++ ){
					if ( datas.get(i).getDefectcode().equalsIgnoreCase( defectDatas.get(j).getDefectcode() ) ){
						datas.get(i).setDescription( defectDatas.get(j).getDescription());
					}
				}
			}
		}
		return datas;
	}
}
